# _*_ coding:utf-8 _*_
# @Time  : 2023.03.29
# @Author: zizlee
import datetime
import json
import pathlib
import random
import time

import pandas as pd
from database import LocalDataBase, DataBaseConnection
from database.variety import VarietyInfo
from database.ths_config import THS_USER, THS_PSWD
from iFinDPy import THS_iFinDLogin, THS_iFinDLogout, THS_HF, THS_HQ

BLOCK_NAMES = {
    '10年期国债': '国债',
    '20号胶': '化工',
    'PTA': '化工',
    'PTA期权': '期权',
    'sc-tas': '化工',
    '上证50指数': '指数',
    '不锈钢': '有色',
    '中证500指数': '指数',
    '中质含硫原油': '化工',
    '乙二醇': '化工',
    '二年期国债': '国债',
    '低硫燃料油': '化工',
    '冶金焦炭': '黑色',
    '原油期权': '期权',
    '国债': '国债',
    '天然橡胶期权': '期权',
    '尿素': '化工',
    '干制红枣': '农产品',
    '强麦(WH)': '农产品',
    '新动力煤': '黑色',
    '晚籼稻': '农产品',
    '普通小麦': '农产品',
    '棉一号': '农产品',
    '棉纱': '农产品',
    '棉花期权': '期权',
    '棕榈油': '农产品',
    '棕榈油期权': '期权',
    '橡胶': '化工',
    '沪深300': '指数',
    '沪深300股指期权': '期权',
    '油菜籽': '农产品',
    '涤纶短纤': '化工',
    '液化石油气': '化工',
    '液化石油气期权': '期权',
    '热轧卷板': '有色',
    '焦煤': '黑色',
    '燃料油': '化工',
    '玉米': '农产品',
    '玉米期权': '期权',
    '玉米淀粉': '农产品',
    '玻璃': '化工',
    '生猪': '农产品',
    '甲醇(MA)': '化工',
    '甲醇期权': '期权',
    '白糖': '农产品',
    '白糖期权': '期权',
    '白银': '贵金属',
    '石油沥青': '化工',
    '硅铁': '黑色',
    '粳稻': '农产品',
    '粳米': '农产品',
    '纤维板': '化工',
    '纯碱': '化工',
    '纸浆': '化工',
    '线材': '有色',
    '细木工板': '化工',
    '聚丙烯': '化工',
    '聚丙烯期权': '期权',
    '聚乙烯': '化工',
    '聚乙烯期权': '期权',
    '聚氯乙烯': '化工',
    '聚氯乙烯期权': '期权',
    '花生': '农产品',
    '苯乙烯': '化工',
    '苹果': '农产品',
    '菜籽油(OI)': '农产品',
    '菜籽粕': '农产品',
    '菜籽粕期权': '期权',
    '螺纹钢': '有色',
    '豆油': '农产品',
    '豆粕': '农产品',
    '豆粕期权': '期权',
    '铁矿石': '黑色',
    '铁矿石期权': '期权',
    '铅': '有色',
    '铜': '有色',
    '铜期权': '期权',
    '铝': '有色',
    '铝期权': '期权',
    '锌': '有色',
    '锌期权': '期权',
    '锡': '有色',
    '锰硅': '黑色',
    '镍': '有色',
    '阴极铜': '有色',
    '鲜鸡蛋': '农产品',
    '黄大豆': '农产品',
    '黄豆2': '农产品',
    '黄金': '贵金属',
    '黄金期权': '期权',
    '动力煤期权': '期权',
    '丁二烯橡胶': '化工',
    '氧化铝': '有色',
    '工业硅': '有色',
    '中证1000指数': '指数',
    '中证1000指数期权': '期权',
    '豆油期权': '期权',
    '白银期权': '期权',
    '上证50指数期权': '期权',
    '螺纹钢期权': '期权',
    '菜籽油期权': '期权',
    '丁二烯橡胶期权': '期权',
    '烧碱': '化工'
}
VARIETY_MULTIPLIER = {v['variety_en']: v['hand_amount'] for v in VarietyInfo}


class HistoryExchangeHandler(object):
    # 交易所
    EX_LIBS = {v['variety_en']: v['ex_code'] for v in VarietyInfo}

    @staticmethod
    def parse_variety_en(row):  # 解析品种
        if row.ex_kind == '期货':
            return ''.join(list(filter(lambda s: s.isalpha(), row.contract))).upper()
        elif row.ex_kind == '期权':
            variety = ''.join(list(filter(lambda s: s.isalpha(), row.contract.split('-')[0]))).upper()
            return f'QQ_{variety}'
        elif row.ex_kind == 'TAS':
            return ''.join(list(filter(lambda s: s.isalpha(), row.contract))).upper().replace('TAS', '')
        else:
            raise ValueError(f'{row.ex_kind}-{row.contract}不存在.')

    def get_ths_quote_code(self, ex_item):
        # 解析出日期和同花顺代码
        if not self.EX_LIBS.get(ex_item['variety_en']):
            raise ValueError(ex_item['variety_en'], '交易所信息不存在.')
        ths_code = ex_item['contract'] + '.' + self.EX_LIBS.get(ex_item['variety_en'])
        if ths_code.endswith('CZC'):  # 合约月份只有3位，且没有中横线
            digit_index = -1
            for i, s in enumerate(ths_code):
                if s.isdigit():
                    digit_index = i
                    break
            if digit_index > -1:
                ths_code = ths_code[:digit_index] + ths_code[digit_index + 1:]
                ths_code = ths_code.replace('-', '')
        if ths_code.endswith('SHF'):  # 没有中横线
            ths_code = ths_code.replace('-', '')
        return ths_code

    # ----------------------------------------------------------

    # 计算追涨杀跌
    @staticmethod
    def calculate_flag_1(item):  # -1-杀跌 0-无 1-追涨
        """
        若开多，判断开仓价格对比当日的最高价格距离，若距离差小于等于当日K线幅度的20%幅度定义为追涨.
        若开空，判断开仓价格对比当日的最低价格距离，若距离差小于等于当日K线幅度的20%幅度定义为杀跌.
        """
        if item['oc_kind'] == '开':
            # K线幅度
            k_diff = item['high'] - item['low']
            if item['direction'] == '买':  # 开多
                p_diff_percent = (item['high'] - item['ex_price']) / k_diff
                return 1 if p_diff_percent <= 0.2 else 0
            if item['direction'] == '卖':  # 开空
                p_diff_percent = (item['ex_price'] - item['low']) / k_diff
                return -1 if p_diff_percent <= 0.2 else 0
        else:
            return 0

    # 计算抄底摸顶
    @staticmethod
    def calculate_flag_2(item):  # -1-抄底 0-无 1-摸顶
        """
        若开多，判断开仓价格对比当日的最低价格距离，若距离差小于等于当日K线幅度的20%幅度定义为抄底.
        若开空，判断开仓价格对比当日的最高价格距离，若距离差小于等于当日K线幅度的20%幅度定义为摸顶
        """
        if item['oc_kind'] == '开':
            # K线幅度
            k_diff = item['high'] - item['low']
            if item['direction'] == '买':  # 开多
                p_diff_percent = (item['ex_price'] - item['low']) / k_diff
                return -1 if p_diff_percent <= 0.2 else 0
            if item['direction'] == '卖':  # 开空
                p_diff_percent = (item['high'] - item['ex_price']) / k_diff
                return 1 if p_diff_percent <= 0.2 else 0
        else:
            return 0

    # 计算急切交易
    def calculate_flag_3(self, item): # -1-平空  0-无 1-平多 2 开仓
        """
        第一种情况:计算每日开盘后5分钟内开仓的成交额.
        第二种情况：计算每日开盘后5分钟内平仓（要分别计算平多和平空）的成交额，计算所有符合条件的平仓的成交金额占总成交金额的比例。
        若比例达到10%或以上，再分2种情况，一是，若平仓属于平多且当时价格若高于前一交易日收盘价格，或若平仓属于平空且当时价格若低于前一交易日收盘价格，将这2种情况的成交额合计，计算其占比总成交额的比例为比例1；二是，若平仓属于平多且当时价格低于前一交易日收盘价格，或平仓属于平空且当时价格高于前一交易日收盘价格，将这2种情况的成交额合计，计算其占比总成交额比例为比例2
        """
        # 开盘后5分钟的记录
        # 股指： 9:30-11:30，13:00-15:00  国债  9:30-11:30，13:00-15:15
        # 商品： 9:00-11:30，13:30-15:00
        ths_code = self.get_ths_quote_code(item)
        if ths_code.endswith('.CFE'):  # 股指
            # 早盘09:30:00-09:36:00
            if '09:30:00' <= item['ex_time'] < '09:36:00':
                if item['oc_kind'] == '开':
                    return 2
                else:
                    return 1 if item['direction'] == '卖' else -1
            else:
                return 0
        else:  # 商品
            # 夜盘21:00:00-21:06:00  早盘09:00:00-09:06:00
            if ('21:00:00' <= item['ex_time'] < '21:06:00') or ('09:00:00' <= item['ex_time'] < '09:06:00'):
                if item['oc_kind'] == '开':
                    return 2
                else:
                    return 1 if item['direction'] == '卖' else -1
            return 0

    # 计算偏好隔夜
    def calculate_flag_4(self, item):  # 0-无  1-开仓
        """
        统计每日收盘前5分钟内开仓的成交额，计算其占总成交金额比例
        """
        if item['oc_kind'] != '开':
            return 0
        ths_code = self.get_ths_quote_code(item)
        if ths_code.endswith('.CFE'):  # 中金所
            if ths_code.startswith('TS') or ths_code.startswith('TF') or ths_code.startswith('T'):
                return 1 if '15:10:00' <= item['ex_time'] < '15:15:00' else 0
            else:
                return 1 if '14:55:00' <= item['ex_time'] < '15:00:00' else 0
        else:  # 商品
            return 1 if '14:55:00' <= item['ex_time'] < '15:00:00' else 0

    # ----------------------------------------------------------
    # 下载日分钟行情
    def get_daily_minute_quote(self, ex_item):  # 获取日分钟K线数据
        # 解析出日期和同花顺代码
        ex_date = datetime.datetime.strptime(str(ex_item['ex_date']), '%Y%m%d').strftime('%Y-%m-%d')
        ths_code = self.get_ths_quote_code(ex_item)
        folder = pathlib.Path(f'src/minutesQuotes/{ex_date.replace("-", "")}/')
        if not folder.exists():
            folder.mkdir(parents=True)
        filename = folder.joinpath(f'{ex_item["contract"]}.csv')
        if filename.exists():
            df = pd.read_csv(filename, encoding='utf8')
            # print(f'DATE={ex_date},C={ex_item["contract"]},从本地读取!{filename}')
        else:
            # 高频序列-期货-开盘价;收盘价;最低价;最高价-iFinD数据接口
            ret = THS_HF(ths_code, 'open;close;low;high', 'Fill:Original,startTime:00:00:00,endTime:23:59:59',
                         f'{ex_date} 00:00:00', f'{ex_date} 23:59:59')
            if ret.errorcode != 0:
                raise ValueError(ret.errmsg + ' ' + str(ret.errorcode) + '\n' + str(ex_date) + ':' + str(ths_code))
            df = ret.data
            if not df.empty:
                df.to_csv(filename, encoding='utf8', index=False)
                print(f'DATE={ex_date},C={ex_item["contract"]},分钟线数据，从同花顺获取...')
            else:
                with open('no_quote.txt', 'a+', encoding='utf8') as lf:
                    lf.write(f'DATE={ex_date},C={ex_item["contract"]}同花顺获取为空。\n')
                return pd.DataFrame()
        return df

    # 下载日k行情，同花顺历史数据
    def get_daily_k_quote(self, ex_item):
        # 解析出日期和同花顺代码
        ex_date = datetime.datetime.strptime(str(ex_item['ex_date']), '%Y%m%d').strftime('%Y-%m-%d')
        ths_code = self.get_ths_quote_code(ex_item)
        folder = pathlib.Path(f'src/dateQuotes/{ex_date.replace("-", "")}/')
        if not folder.exists():
            folder.mkdir(parents=True)
        filename = folder.joinpath(f'{ex_item["contract"]}.csv')
        if filename.exists():
            df = pd.read_csv(filename, encoding='utf8')
            # print(f'DATE={ex_date},C={ex_item["contract"]},从本地读取!{filename}')
        else:
            ret = THS_HQ(ths_code, 'preClose,open,high,low,close', '', f'{ex_date}', f'{ex_date}')
            df = ret.data
            if not df.empty:
                df.to_csv(filename, encoding='utf8', index=False)
                print(f'DATE={ex_date},C={ex_item["contract"]},从同花顺获取...')
            else:
                with open('no_quote.txt', 'a+', encoding='utf8') as lf:
                    lf.write(f'DATE={ex_date},C={ex_item["contract"]}同花顺获取为空。\n')
                return pd.DataFrame()
        return df


def parse_variety_en(row):  # 解析品种
    if row['ex_kind'] == '期货':
        return ''.join(list(filter(lambda s: s.isalpha(), row['contract']))).upper()
    elif row['ex_kind'] == '期权':
        variety = ''.join(list(filter(lambda s: s.isalpha(), row['contract'].split('-')[0]))).upper()
        return f'QQ_{variety}'
    elif row['ex_kind'] == 'TAS':
        return ''.join(list(filter(lambda s: s.isalpha(), row['contract']))).upper().replace('TAS', '')
    else:
        raise ValueError(f'{row.ex_kind}-{row.contract}不存在.')


def get_variety_hand_amount(ven):  # 获取合约乘数，写在这方便抛出不存在错误
    amount = VARIETY_MULTIPLIER.get(ven)
    if not amount:
        raise ValueError('NOT FOUND {} hand amount!'.format(ven))
    return amount


# 处理资金对账表数据
def zjdz_get_handler(h_date: datetime.date, cust_num: str):
    print('{} | 开始处理{}({})资金对账表数据...'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                             h_date.strftime('%Y-%m-%d'), cust_num))
    # 1 本地查询得到数据后处理成ret所需文件数据保存
    with LocalDataBase() as c:
        c.execute('SELECT * FROM fund_balance WHERE ex_date=%s AND cust_num=%s;', (h_date.strftime('%Y%m%d'), cust_num))
        zjdz_data = list(c.fetchall())
    # 2 将数据处理成需要的结果

    ret_data = []
    for row in zjdz_data:
        ret_data.append({
            'ex_date': row['ex_date'],
            'fund_num': row['cust_num'],
            'fund_name': '',
            'equity_begin': row['sett_previous'],
            'equity_end': row['equity_end'],
            'fund_in': row['fund_in'],
            'fund_out': row['fund_out'],
            'deposit_fn': row['fund_bail'],
            'deposit_ex': row['fund_bail_exl'],
            'balance': row['sett_current'],
            'profit': row['profit'],
            'profit_close': row['profit_close'],
            'profit_drift': row['profit_drift'],
            'charge': row['charge']
        })
    # 3 保存
    if not pathlib.Path('ret/FundBalance').exists():
        pathlib.Path('ret/FundBalance').mkdir(parents=True)
    with open('ret/FundBalance/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'w', encoding='utf8') as ret_zjdz:
        json.dump(ret_data, ret_zjdz, ensure_ascii=False, indent=2)
    print('{} | 处理{}({})资金对账表数据完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                           h_date.strftime('%Y-%m-%d'), cust_num))


# 处理交易统计表
def jytj_get_handler(h_date: datetime.date, cust_num: str):
    print('{} | 开始处理{}({})交易统计表数据...'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                             h_date.strftime('%Y-%m-%d'), cust_num))
    # 1 本地查询得到数据后处理成ret所需文件数据保存
    with LocalDataBase() as c:
        c.execute('SELECT * FROM exchange_total WHERE ex_date=%s AND cust_num=%s;', (h_date.strftime('%Y%m%d'), cust_num))
        jytj_data = list(c.fetchall())
    # 2 将数据处理成需要的结果
    ret_data = []
    for row in jytj_data:
        # 获取板块名称
        block_name = BLOCK_NAMES.get(row['variety_name'])
        if not block_name:
            raise ValueError(f'{row["variety_name"]}板块名称不存在!')
        ret_data.append({
            'ex_date': row['ex_date'],
            'fund_num': row['cust_num'],
            'fund_name': '',
            'variety_name': row['variety_name'],
            'block_name': block_name,
            'amount_ex': row['ex_amount'],
            'volume_ex': row['hands'],
            'volume_hd': row['hands_holdings'],
            'deposit_fn': row['fund_bail'],
            'deposit_ex': row['fund_bail_exl'],
            'profit_close': row['profit'],
            'profit_drift': row['profit_drift'],
            'charge': row['charge']
        })
    # 3 保存
    if not pathlib.Path('ret/ExchangeTotal').exists():
        pathlib.Path('ret/ExchangeTotal').mkdir(parents=True)
    with open('ret/ExchangeTotal/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'w', encoding='utf8') as ret_jytj:
        json.dump(ret_data, ret_jytj, ensure_ascii=False, indent=2)
    print('{} | 处理{}({})交易统计表数据完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                           h_date.strftime('%Y-%m-%d'), cust_num))


# 成交明细表
def cjmx_get_handler(h_date: datetime.date, cust_num: str):
    print('{} | 开始处理{}({})成交明细表数据...'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                             h_date.strftime('%Y-%m-%d'), cust_num))
    # 1 本地查询得到数据后处理成ret所需文件数据保存
    with LocalDataBase() as c:
        c.execute('SELECT * FROM exchange_detail WHERE ex_date=%s AND cust_num=%s;', (h_date.strftime('%Y%m%d'), cust_num))
        cjmx_data = list(c.fetchall())
    # 2 将数据处理成需要的结果
    ret_data = []
    for row in cjmx_data:
        ret_data.append({
            'ex_date': row['ex_date'],
            'fund_num': row['cust_num'],
            'fund_name': '',
            'ex_kind': row['ex_kind'],
            'volume': row['hands'],
            'ex_price': row['ex_price'],
            'ex_money': row['ex_amount'],
            'charge': row['charge'],
            'variety_name': row['variety_name'],
            'contract': row['contract'],
            'direction': row['direction'],
            'oc_kind': row['oc_kind']
        })
    # 3 保存
    if not pathlib.Path('ret/ExchangeDetail').exists():
        pathlib.Path('ret/ExchangeDetail').mkdir(parents=True)
    with open('ret/ExchangeDetail/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'w', encoding='utf8') as ret_cjmx:
        json.dump(ret_data, ret_cjmx, ensure_ascii=False, indent=2)
    print('{} | 处理{}({})成交明细表数据完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                           h_date.strftime('%Y-%m-%d'), cust_num))


# 平仓明细表
def pcmx_get_handler(h_date: datetime.date, cust_num: str):
    print('{} | 开始处理{}({})平仓明细表数据...'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                             h_date.strftime('%Y-%m-%d'), cust_num))
    # 1 本地查询得到数据后处理成ret所需文件数据保存
    with LocalDataBase() as c:
        c.execute('SELECT * FROM close_detail WHERE ex_date=%s AND cust_num=%s;', (h_date.strftime('%Y%m%d'), cust_num))
        pcmx_data = list(c.fetchall())
    # 2 将数据处理成需要的结果
    ret_data = []
    for row in pcmx_data:
        # 计算平仓成交额
        variety_en = parse_variety_en(row)
        # 合约乘数
        contract_mul = get_variety_hand_amount(variety_en)
        amount_close = round(row['price_close'] * row['hands'] * contract_mul, 2)  # 平仓成交额=平仓价格*手数*合约乘数
        # 开仓成交额
        amount_open = round(row['price_open'] * row['hands'] * contract_mul, 2)
        ret_data.append({
            'ex_date': row['ex_date'],
            'fund_num': row['cust_num'],
            'fund_name': '',
            'variety_name': row['variety_name'],
            'contract': row['contract'],
            'direction': row['direction'],
            'volume': row['hands'],
            'price_open': row['price_open'],
            'price_close': row['price_close'],
            'profit_close': row['profit_close'],
            'amount_close': amount_close,
            'amount_open': amount_open,
            'date_open': row['date_open'],
        })
    # 3 保存
    if not pathlib.Path('ret/CloseDetail').exists():
        pathlib.Path('ret/CloseDetail').mkdir(parents=True)
    with open('ret/CloseDetail/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'w', encoding='utf8') as ret_pcmx:
        json.dump(ret_data, ret_pcmx, ensure_ascii=False, indent=2)
    print('{} | 处理{}({})平仓明细表数据完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                           h_date.strftime('%Y-%m-%d'), cust_num))


# 成交单管理
def cjdgl_get_handler(h_date: datetime.date, cust_num: str):
    print('{} | 开始处理{}({})成交单管理数据...'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                             h_date.strftime('%Y-%m-%d'), cust_num))
    # 1 本地查询得到数据后处理成ret所需文件数据保存
    with LocalDataBase() as c:
        c.execute('SELECT * FROM exchange_history WHERE ex_date=%s AND cust_num=%s;', (h_date.strftime('%Y%m%d'), cust_num))
        cjdgl_data = list(c.fetchall())
    # 2 将数据处理成需要的结果
    # ret = THS_iFinDLogin(THS_USER, THS_PSWD)
    # if ret != 0:
    #     raise ValueError('登录同花顺失败{}'.format(ret))
    handler = HistoryExchangeHandler()
    ret_data = []
    for row in cjdgl_data:
        row['variety_en'] = parse_variety_en(row)
        if row['ex_kind'] != 'TAS':
            handler.get_daily_minute_quote(row)  # 获取分钟k线行情
            quote = handler.get_daily_k_quote(row)  # 获取当分钟k线行情数据
            # print(quote)
            row['pre_close'] = quote.iloc[0]['preClose']
            row['open'] = quote.iloc[0]['open']
            row['high'] = quote.iloc[0]['high']
            row['low'] = quote.iloc[0]['low']
            row['close'] = quote.iloc[0]['close']
            # 如果没有开盘价就使用前日收盘价
            if not row['open'] and str(row['open']) != '0':
                row['open'] = row['pre_close']
            # 如果没有最高价，取开盘和收盘的最高值
            if not row['high'] and str(row['high']) != '0':
                row['high'] = max([row['open'], row['close']])
            # 如果没有最底价，取开盘和收盘最底价
            if not row['low'] and str(row['low']) != 0:
                row['low'] = min([row['open'], row['close']])
            # 如果没有前开盘价,取开盘价
            if not row['pre_close']:
                row['pre_close'] = row['open']

            row['pre_close'] = float(row['pre_close'])
            row['open'] = float(row['open'])
            row['high'] = float(row['high'])
            row['low'] = float(row['low'])
            row['close'] = float(row['close'])

            row['flag1'] = handler.calculate_flag_1(row)  # -1-杀跌 0-无 1-追涨
            row['flag2'] = handler.calculate_flag_2(row)  # -1-抄底 0-无 1-摸顶
            row['flag3'] = handler.calculate_flag_3(row)  # -1-平空 0-无 1-平多 2 开盘前五分钟开仓
            row['flag4'] = handler.calculate_flag_4(row)  # 0-无  1-收盘前五分钟开仓
            if row['ex_price'] < row['low'] or row['ex_price'] > row['high']:
                # raise ValueError(
                #     f'-----{row["ex_date"]} {row["contract"]} 成交价{row["ex_price"]} < 最低价{row["low"]} or 成交价{row["ex_price"]} > 最高价{row["high"]}-----')
                print('-----------' * 20)
                with open('log.log', 'a+', encoding='utf8') as log:
                    log.write(
                        f'{row["ex_date"]} {row["contract"]} 成交价{row["ex_price"]} < 最低价{row["low"]} or 成交价{row["ex_price"]} > 最高价{row["high"]}\n')
        else:
            row['pre_close'] = 0
            row['open'] = 0
            row['high'] = 0
            row['low'] = 0
            row['close'] = 0
            row['flag1'] = 0  # -1-杀跌 0-无 1-追涨
            row['flag2'] = 0  # -1-抄底 0-无 1-摸顶
            row['flag3'] = 0  # -1-平空 0-无 1-平多 2 开盘前五分钟开仓
            row['flag4'] = 0  # 0-无  1-收盘前五分钟开仓

        ret_data.append({
            'ex_date': row['ex_date'],
            'ex_time': row['ex_time'],
            'cust_num': row['cust_num'],
            'ex_name': row['exl_name'],
            'ex_kind': row['ex_kind'],
            'variety_name': row['variety_name'],
            'contract': row['contract'],
            'direction': row['direction'],
            'oc_kind': row['oc_kind'],
            'hands': row['hands'],
            'ex_price': row['ex_price'],
            'ex_amount': row['ex_amount'],
            'charge': row['charge'],
            'ex_charge': row['ex_charge'],
            'variety_en': row['variety_en'],
            'pre_close': row['pre_close'],
            'open_price': row['open'],
            'high': row['high'],
            'low': row['low'],
            'close_price': row['close'],
            'flag1': row['flag1'],
            'flag2': row['flag2'],
            'flag3': row['flag3'],
            'flag4': row['flag4'],
        })
    # THS_iFinDLogout()
    # 3 保存
    if not pathlib.Path('ret/HistoryExchange').exists():
        pathlib.Path('ret/HistoryExchange').mkdir(parents=True)
    with open('ret/HistoryExchange/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'w', encoding='utf8') as ret_cjdgl:
        json.dump(ret_data, ret_cjdgl, ensure_ascii=False, indent=2)
    print('{} | 处理{}({})成交单管理数据完成!'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                                           h_date.strftime('%Y-%m-%d'), cust_num))


# 保存成交单管理
def save_cjdgl(h_date: datetime.date, cust_num: str):
    with open('ret/HistoryExchange/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'r',
              encoding='utf8') as ret_file:
        cjdgl_data = json.load(ret_file)
    if len(cjdgl_data) < 1:
        print('{}({})无成交单管理数据.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
        return
    sql = """
        INSERT INTO ac_exhy (ex_date,ex_time,cust_num,ex_name,ex_kind,variety_name,contract,direction,oc_kind,
          hands,ex_price,ex_amount,charge,ex_charge,variety_en,pre_close,open_price,high,low,close_price,
          flag1,flag2,flag3,flag4) VALUES (%(ex_date)s,%(ex_time)s,%(cust_num)s,%(ex_name)s,%(ex_kind)s,
          %(variety_name)s,%(contract)s,%(direction)s,%(oc_kind)s,%(hands)s,%(ex_price)s,%(ex_amount)s,%(charge)s,
          %(ex_charge)s,%(variety_en)s,%(pre_close)s,%(open_price)s,%(high)s,%(low)s,%(close_price)s,
          %(flag1)s,%(flag2)s,%(flag3)s,%(flag4)s);
    """
    print('准备保存{}({})成交单管理表，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, len(cjdgl_data)))
    with DataBaseConnection() as cursor:
        cursor.execute('SELECT id FROM ac_exhy WHERE ex_date=%s AND cust_num=%s LIMIT 1;',
                       (h_date.strftime('%Y%m%d'), cust_num))
        is_exist = cursor.fetchone()
        if is_exist:
            print('{}({})成交单管理数据已存在,无需重复保存.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
            return
        c = cursor.executemany(sql, cjdgl_data)
    print('保存{}({})成交单管理表成功，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, c))


# 保存平仓明细表
def save_pcmx(h_date: datetime.date, cust_num: str):
    with open('ret/CloseDetail/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'r',
              encoding='utf8') as ret_file:
        pcmx_data = json.load(ret_file)
    if len(pcmx_data) < 1:
        print('{}({})无平仓明细数据.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
        return
    sql = """
        INSERT INTO ac_clex
        (ex_date,fund_num,fund_name,variety_name,contract,direction,
         volume,price_open,price_close,profit_close,amount_close,amount_open,date_open)
        VALUES
        (%(ex_date)s,%(fund_num)s,%(fund_name)s,%(variety_name)s,%(contract)s,%(direction)s,
         %(volume)s,%(price_open)s,%(price_close)s,%(profit_close)s,%(amount_close)s,%(amount_open)s,%(date_open)s);
    """
    print('准备保存{}({})平仓明细表，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, len(pcmx_data)))
    with DataBaseConnection() as cursor:
        cursor.execute('SELECT id FROM ac_clex WHERE ex_date=%s AND fund_num=%s LIMIT 1;',
                       (h_date.strftime('%Y%m%d'), cust_num))
        is_exist = cursor.fetchone()
        if is_exist:
            print('{}({})平仓明细表数据已存在,无需重复保存.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
            return
        c = cursor.executemany(sql, pcmx_data)
    print('保存{}({})平仓明细表成功，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, c))


# 保存成交明细表
def save_cjmx(h_date: datetime.date, cust_num: str):
    with open('ret/ExchangeDetail/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'r',
              encoding='utf8') as ret_file:
        cjmx_data = json.load(ret_file)
    if len(cjmx_data) < 1:
        print('{}({})无成交明细数据.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
        return
    sql = """
        INSERT INTO ac_tnor
        (ex_date,fund_num,fund_name,ex_kind,volume,ex_price,ex_money,
         charge,variety_name,contract,direction,oc_kind)
        VALUES
        (%(ex_date)s,%(fund_num)s,%(fund_name)s,%(ex_kind)s,%(volume)s,%(ex_price)s,%(ex_money)s,
         %(charge)s,%(variety_name)s,%(contract)s,%(direction)s,%(oc_kind)s);
    """
    print('准备保存{}({})成交明细表，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, len(cjmx_data)))
    with DataBaseConnection() as cursor:
        cursor.execute('SELECT id FROM ac_tnor WHERE ex_date=%s AND fund_num=%s LIMIT 1;',
                       (h_date.strftime('%Y%m%d'), cust_num))
        is_exist = cursor.fetchone()
        if is_exist:
            print('{}({})成交明细表数据已存在,无需重复保存.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
            return
        c = cursor.executemany(sql, cjmx_data)
    print('保存{}({})成交明细表成功，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, c))


# 保存交易统计表
def save_jytj(h_date: datetime.date, cust_num: str):
    with open('ret/ExchangeTotal/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'r',
              encoding='utf8') as ret_file:
        jytj_data = json.load(ret_file)
    if len(jytj_data) < 1:
        print('{}({})无交易统计数据.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
        return
    sql = """
        INSERT INTO ac_excp (ex_date,fund_num,fund_name,variety_name,block_name,amount_ex,volume_ex,volume_hd,
          deposit_fn,deposit_ex,profit_close,profit_drift,charge) VALUES (%(ex_date)s,%(fund_num)s,%(fund_name)s,
          %(variety_name)s,%(block_name)s,%(amount_ex)s,%(volume_ex)s,%(volume_hd)s,
          %(deposit_fn)s,%(deposit_ex)s,%(profit_close)s,%(profit_drift)s,%(charge)s);
    """
    print('准备保存{}({})交易统计表，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, len(jytj_data)))
    with DataBaseConnection() as cursor:
        cursor.execute('SELECT id FROM ac_excp WHERE ex_date=%s AND fund_num=%s LIMIT 1;',
                       (h_date.strftime('%Y%m%d'), cust_num))
        is_exist = cursor.fetchone()
        if is_exist:
            print('{}({})交易统计表数据已存在,无需重复保存.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
            return
        c = cursor.executemany(sql, jytj_data)
    print('保存{}({})交易统计表成功，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, c))


# 保存资金对帐表
def save_zjdz(h_date: datetime.date, cust_num: str):
    with open('ret/FundBalance/{}_{}.json'.format(h_date.strftime('%Y%m%d'), cust_num), 'r',
              encoding='utf8') as ret_file:
        zjdz_data = json.load(ret_file)
    if len(zjdz_data) < 1:
        print('{}({})资金对帐数据.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
        return
    sql = """
        INSERT INTO ac_fund
        (ex_date,fund_num,fund_name,equity_begin,equity_end,fund_in,fund_out,
         deposit_fn,deposit_ex,balance,profit,profit_close,profit_drift,charge)
        VALUES 
        (%(ex_date)s,%(fund_num)s,%(fund_name)s,%(equity_begin)s,%(equity_end)s,%(fund_in)s,%(fund_out)s,
         %(deposit_fn)s,%(deposit_ex)s,%(balance)s,%(profit)s,%(profit_close)s,%(profit_drift)s,%(charge)s);
    """
    print('准备保存{}({})资金对帐表，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, len(zjdz_data)))
    with DataBaseConnection() as cursor:
        cursor.execute('SELECT id FROM ac_fund WHERE ex_date=%s AND fund_num=%s LIMIT 1;',
                       (h_date.strftime('%Y%m%d'), cust_num))
        is_exist = cursor.fetchone()
        if is_exist:
            print('{}({})资金对帐数据已存在,无需重复保存.'.format(h_date.strftime('%Y-%m-%d'), cust_num))
            return
        c = cursor.executemany(sql, zjdz_data)
    print('保存{}({})资金对帐表成功，数量：{}'.format(h_date.strftime('%Y-%m-%d'), cust_num, c))


if __name__ == '__main__':
    with open('ex_dates.json', 'r', encoding='utf8') as dtf:
        date_dict = json.load(dtf)

    date_list = date_dict['2023']

    cust_list = ['70000792']
    # ret = THS_iFinDLogin(THS_USER, THS_PSWD)
    # if ret != 0:
    #     raise ValueError('登录同花顺失败{}'.format(ret))
    for d in date_list:
        d_date = datetime.date(int(d[0:4]), int(d[4:6]), int(d[6:8]))
        if d_date < datetime.date(2023, 9, 15):
            continue
        # if d_date > datetime.date(2022, 12, 31):
        #     continue

        # 先处理目标RET的数据
        for cust in cust_list:
            zjdz_get_handler(h_date=d_date, cust_num=cust)
            time.sleep(0.5)
            jytj_get_handler(h_date=d_date, cust_num=cust)
            time.sleep(0.5)
            # cjmx_get_handler(h_date=d_date, cust_num=cust)
            # time.sleep(0.5)
            pcmx_get_handler(h_date=d_date, cust_num=cust)
            time.sleep(0.5)
            # cjdgl_get_handler(h_date=d_date, cust_num=cust)
            # time.sleep(0.5)

        # # 将目标RET数据入库
        # for cust in cust_list:
        #     save_cjdgl(h_date=d_date, cust_num=cust)
        #     save_pcmx(h_date=d_date, cust_num=cust)
        #     save_cjmx(h_date=d_date, cust_num=cust)
        #     save_jytj(h_date=d_date, cust_num=cust)
        #     save_zjdz(h_date=d_date, cust_num=cust)
    # THS_iFinDLogout()
